#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : countjgdata.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/4/16 9:59                 #
#                                                                   #
#                   @Last Update  : 2021/4/16 9:59                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes: 分语种统计加工数据库数据加工情况                                                         #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime

import pymysql

from PythonWebTools.settings import JGMYSQL_CONNEXT
from countdata.models import jgdata


class Countjgsqldata:

    def __init__(self):
        self.table = ["`law`", "`case`", "`treaty`"]
        self.jgconn = pymysql.connect(host=JGMYSQL_CONNEXT['MYSQL_HOST'], port=JGMYSQL_CONNEXT['MYSQL_PORT'],
                                      db=JGMYSQL_CONNEXT['MYSQL_DB'], user=JGMYSQL_CONNEXT['MYSQL_USER'],
                                      passwd=JGMYSQL_CONNEXT['MYSQL_PASSWD'], charset='utf8')
        self.jgcour = self.jgconn.cursor()

    def __del__(self):
        self.jgcour.close()
        self.jgconn.close()

    def __reConnect(self):
        """
        查看数据库连接是否有效
        :return:
        """
        try:
            self.jgconn.ping()
        except:
            self.jgconn()

    def __countsql(self, tablename):
        """
        分语种统计数据加工情况
        :param tablename:   统计的表名
        :return:resultdict: 分语种的数据加工情况字典
        """
        resultdict = {}
        sql = '''select DISTINCT sortB from %s''' % tablename    # 查询语种分类
        try:
            self.jgcour.execute(sql)
            languageS = self.jgcour.fetchall()
        except Exception as e:
            print("查询语种数量异常:" + str(e))
            return resultdict
        # 分语种统计数据加工情况
        for language in languageS:
            language = language[0]
            languagedict = {}
            print("查询%s表的%s语言" % (tablename, language))
            sql = '''select count(*) from %s where sortB = "%s" and (SYS_FLD_MARKSTATE = "33" or SYS_FLD_MARKSTATE = "0" or SYS_FLD_MARKSTATE = "1")''' % (
            tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["未标引（33，0，1）"] = counts[0][0]
            except Exception as e:
                languagedict["未标引（33，0，1）"] = "-1"
                print("查询%s未标引数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and SYS_FLD_MARKSTATE = "999"''' % (tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["已标引未翻译（999）"] = counts[0][0]
            except Exception as e:
                languagedict["已标引未翻译（999）"] = "-1"
                print("查询%s已标引未翻译数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and (SYS_FLD_MARKSTATE = "2" or SYS_FLD_MARKSTATE = "3")''' % (
            tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["已翻译未校对（2，3）"] = counts[0][0]
            except Exception as e:
                languagedict["已翻译未校对（2，3）"] = "-1"
                print("查询%s已翻译未校对数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and (SYS_FLD_MARKSTATE = "4" or SYS_FLD_MARKSTATE = "5")''' % (
            tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["已校对未初审（4，5）"] = counts[0][0]
            except Exception as e:
                languagedict["已校对未初审（4，5）"] = "-1"
                print("查询%s已校对未初审数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and (SYS_FLD_MARKSTATE = "6" or SYS_FLD_MARKSTATE = "7")''' % (
                tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["已初审未终审（6，7）"] = counts[0][0]
            except Exception as e:
                languagedict["已初审未终审（6，7）"] = "-1"
                print("查询%s已初审未终审数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and SYS_FLD_MARKSTATE = "8"''' % (tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["入库量（8）"] = counts[0][0]
            except Exception as e:
                languagedict["入库量（8）"] = "-1"
                print("查询%s入库量数量异常" % language + str(e))
            sql = '''select count(*) from %s where sortB = "%s" and SYS_FLD_MARKSTATE != "111"''' % (
                tablename, language)
            try:
                self.jgcour.execute(sql)
                counts = self.jgcour.fetchall()
                languagedict["总量"] = counts[0][0]
            except Exception as e:
                languagedict["总量"] = "-1"
                print("查询%s总量数量异常" % language + str(e))
            resultdict[language] = languagedict
        return resultdict

    def __writein(self, datadict:dict, table, date):
        """
        将统计结果写入数据库中
        :param datadict: 统计结果字典
        :param table: 统计的表名
        :param date: 统计日期
        :return:
        """
        for language in datadict.keys():    # 分语种依次写入数据
            for data_status in datadict[language].keys():
                data = datadict[language][data_status]
                test1 = jgdata(table=table, language=language, data_status=data_status, data=data, count_date=date)
                test1.save()


    def countvalue(self):
        """
        统计数据函数
        :return:
        """
        date_now = datetime.date.today()    # 统计日期
        for table in self.table:
            table_dict = {}
            table_dict = self.__countsql(table)
            self.__writein(table_dict, table, date_now)
